package Databasehandler;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.GregorianCalendar;

/**
 * En MySQL-implementation af DBCommunicator-interfacet.
 *
 * @author Daniel Koch
 */
public class MySQLCommunicator implements DBCommunicator {
    private DBConnector dbc;

    /**
     * Initialiserer dbc-variablen med et DBConnector-objekt.
     *
     * @param dbc
     */
    public MySQLCommunicator(DBConnector dbc) {
        this.dbc = dbc;
    }

    /**
     * Konverterer et GregorianCalendar-objekt til en String.
     * @param gc
     * @return String
     */
    private String dateToString(GregorianCalendar gc) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return format.format(gc.getTime());
    }

    //<editor-fold desc="Kunde">

    /**
     * Opretter en kunde vha. SQL
     *
     * @param customerName
     * @throws Exception
     */
    public void createCustomer(String customerName) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("INSERT INTO tbl_customer(customerName) VALUES(?)");
        pstmt.setString(1, customerName);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    /**
     * Redigérer en kunde vha. SQL.
     *
     * @param customerID
     * @param customerName
     * @throws Exception
     */
    public void editCustomer(int customerID, String customerName) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("UPDATE tbl_customer SET customerName=? WHERE customerID=?");
        pstmt.setString(1, customerName);
        pstmt.setInt(2, customerID);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    /**
     * Sletter en kunde vha. SQL.
     *
     * @param customerID
     * @throws Exception
     */
    public void deleteCustomer(int customerID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("DELETE FROM tbl_customer WHERE customerID=?");
        pstmt.setInt(1, customerID);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    /**
     * Henter en kunde vha. SQL.
     *
     * @param customerID
     * @return Et ResultSet-objekt med én kunde.
     * @throws Exception
     */
    public ResultSet getCustomer(int customerID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_customer WHERE customerID=?");
        pstmt.setInt(1, customerID);
        ResultSet customerDetails = pstmt.executeQuery();
        this.dbc.closeConn();
        return customerDetails;
    }

    /** Henter alle kunder vha. SQL.
     *
     * @return Et ResultSet-objekt med alle kunder.
     * @throws Exception
     */
    public ResultSet getCustomers() throws SQLException {
        Statement stmt = this.dbc.createStatement();
        ResultSet customers =  stmt.executeQuery("SELECT * FROM tbl_customer");
        this.dbc.closeConn();
        return customers;
    }

    /**
     * Henter alle aktuelle kunder vha SQL.
     *
     * @return Et ResultSet-objekt med alle aktuelle kunder
     * @throws Exception
     */
    public ResultSet getCurrentCustomers() throws SQLException {
        Statement stmt = this.dbc.createStatement();
        ResultSet customers =  stmt.executeQuery("SELECT * FROM tbl_customer WHERE customerID IN (SELECT customerID FROM view_current)");
        this.dbc.closeConn();
        return customers;
    }

    //</editor-fold>

    //<editor-fold desc="Batchrapportsystem">

    /**
     * Opretter et batchrapportsystem vha SQL.
     *
     * @param batchReportSystemName
     * @param batchReportIntervalID
     * @param customerID
     * @throws Exception
     */
    public void createBatchReportSystem(String batchReportSystemName, int batchReportIntervalID, int customerID) throws SQLException{
        PreparedStatement pstmt = this.dbc.createPreparedStatement("INSERT INTO tbl_batchreportsystem(batchReportSystemName, FK_batchReportIntervalID, FK_customerID) VALUES(?, ?, ?)");
        pstmt.setString(1, batchReportSystemName);
        pstmt.setInt(2, batchReportIntervalID);
        pstmt.setInt(3, customerID);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    /**
     * Redigérer et batchrapportsystem vha. SQL.
     *
     * @param batchReportSystemID
     * @param batchReportSystemName
     * @param batchReportIntervalID
     * @throws Exception
     */
    public void editBatchReportSystem(int batchReportSystemID, String batchReportSystemName, int batchReportIntervalID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("UPDATE tbl_batchreportsystem SET batchReportSystemName=?, FK_batchReportIntervalID=? WHERE batchReportSystemID=?");
        pstmt.setString(1, batchReportSystemName);
        pstmt.setInt(2, batchReportIntervalID);
        pstmt.setInt(3, batchReportSystemID);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    /**
     * Sletter et batchrapportsystem vha. SQL.
     *
     * @param batchSystemID
     * @throws Exception
     */
    public void deleteBatchReportSystem(int batchReportSystemID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("DELETE FROM tbl_batchreportsystem WHERE batchReportSystemID=?");
        pstmt.setInt(1, batchReportSystemID);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    /**
     * Henter et batchrapportsystem vha. SQL.
     *
     * @param batchSystemID
     * @return Et ResultSet-objekt med ét batchrapportsystem
     * @throws Exception
     */
    public ResultSet getBatchReportSystem(int batchReportSystemID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_batchreportsystem WHERE batchReportSystemID=?");
        pstmt.setInt(1, batchReportSystemID);
        ResultSet batchReportSystemDetails = pstmt.executeQuery();
        this.dbc.closeConn();
        return batchReportSystemDetails;
    }

    /**
     * Henter alle batchrapportsystemer vha. SQL.
     *
     * @return Et ResultSet-objekt med alle batchrapportsystemer
     * @throws Exception
     */
    public ResultSet getBatchReportSystems() throws SQLException {
        Statement stmt = this.dbc.createStatement();
        ResultSet batchReportSystems =  stmt.executeQuery("SELECT * FROM tbl_batchreportsystem");
        this.dbc.closeConn();
        return batchReportSystems;
    }

     /**
      * Henter alle batchrapportsystemer for en kunde vha. SQL.
      *
      * @param customerID
      * @return Et ResultSet-objekt med alle batchrapportsystemer for en kunde.
      * @throws Exception
      */
    public ResultSet getCustomerBatchReportSystems(int customerID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_batchreportsystem WHERE FK_customerID=?");
        pstmt.setInt(1, customerID);
        ResultSet batchReportSystems = pstmt.executeQuery();
        this.dbc.closeConn();
        return batchReportSystems;
    }

    /**
     * Henter alle aktuelle batchrapportsystmer vha. SQL.
     *
     * @return Et ResultSet-objekt med alle aktuelle batchrapportsystemer.
     * @throws Exception
     */
    public ResultSet getCurrentBatchReportSystems() throws SQLException {
        Statement stmt = this.dbc.createStatement();
        ResultSet batchReportSystems =  stmt.executeQuery("SELECT * FROM tbl_batchreportsystem WHERE batchReportSystemID IN (SELECT batchReportSystemID FROM view_current)");
        this.dbc.closeConn();
        return batchReportSystems;
    }

    /**
     * Henter ét batchrapportsystemid.
     *
     * @return Et int objekt for det søgte batchrapportnavn
     * @throws Exception
     */
    public int getBatchReportSystemID(String batchReportName) throws SQLException
    {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT BatchReportSystemID FROM tbl_batchreportsystem WHERE BatchReportSystemName=?");
        pstmt.setString(1, batchReportName);
        ResultSet rs =  pstmt.executeQuery();
        int batchReportSystemID = 0;
        while(rs.next())
         batchReportSystemID = rs.getInt(1);
        this.dbc.closeConn();
        return batchReportSystemID;
    }

    /**
     * Henter alle aktuelle batchrapportsystemer for en kunde vha. SQL.
     *
     * @param customerID
     * @return Et ResultSet-objekt med alle aktuelle batchrapportsystemer for en kunde.
     * @throws Exception
     */
    public ResultSet getCurrentCustomerBatchReportSystems(int customerID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_batchreportsystem WHERE batchReportSystemID IN (SELECT batchReportSystemID FROM view_current WHERE customerID=?)");
        pstmt.setInt(1, customerID);
        ResultSet batchReportSystems =  pstmt.executeQuery();
        this.dbc.closeConn();
        return batchReportSystems;
    }

    //</editor-fold>

    //<editor-fold desc="Abonnent">

    /**
     * Opretter en abonnent vha. SQL.
     *
     * @param subscriberName
     * @param customerID
     * @param email
     * @throws Exception
     */
    public void createOBSReportSubscriber(String OBSReportSubscriberName, int customerID, String email) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("INSERT INTO tbl_obsreportsubscriber(OBSReportSubscriberName, FK_customerID, email) VALUES(?, ?, ?)");
        pstmt.setString(1, OBSReportSubscriberName);
        pstmt.setInt(2, customerID);
        pstmt.setString(3, email);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    /**
     * Redigérer en abonnent vha. SQL.
     *
     * @param subscriberID
     * @param subscriberName
     * @param email
     * @throws Exception
     */
    public void editOBSReportSubscriber(int OBSReportSubscriberID, String OBSReportSubscriberName, String email) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("UPDATE tbl_obsreportsubscriber SET OBSReportSubscriberName=?, email=? WHERE OBSReportSubscriberID=?");
        pstmt.setString(1, OBSReportSubscriberName);
        pstmt.setString(2, email);
        pstmt.setInt(3, OBSReportSubscriberID);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    /**
     * Sletter en abonnent vha. SQL.
     *
     * @param subscriberID
     * @throws Exception
     */
    public void deleteOBSReportSubscriber(int OBSReportSubscriberID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("DELETE FROM tbl_obsreportsubscriber WHERE OBSReportSubscriberID=?");
        pstmt.setInt(1, OBSReportSubscriberID);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    /**
     * Henter en abonnent vha. SQL.
     *
     * @param subscriberID
     * @return Et ResultSet-objekt med én abonnent
     * @throws Exception
     */
    public ResultSet getOBSReportSubscriber(int OBSReportSubscriberID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_obsreportsubscriber WHERE OBSReportSubscriberID=?");
        pstmt.setInt(1, OBSReportSubscriberID);
        ResultSet OBSReportSubscriberDetails = pstmt.executeQuery();
        this.dbc.closeConn();
        return OBSReportSubscriberDetails;
    }

    /**
     * Henter alle abonnenter vha. SQL.
     *
     * @return Et ResultSet-objekt med alle abonnenter.
     * @throws Exception
     */
    public ResultSet getOBSReportSubscribers() throws SQLException {
        Statement stmt = this.dbc.createStatement();
        ResultSet OBSReportSubscribers = stmt.executeQuery("SELECT * FROM tbl_obsreportsubscriber");
        this.dbc.closeConn();
        return OBSReportSubscribers;
    }

    /**
     * Henter alle abonnenter for en kunde vha. SQL.
     *
     * @param customerID
     * @return Et ResultSet-objekt med alle abonnenter for en kunde.
     * @throws Exception
     */
    public ResultSet getCustomerOBSReportSubscribers(int customerID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_obsreportsubscriber WHERE FK_customerID=?");
        pstmt.setInt(1, customerID);
        ResultSet OBSReportSubscribers = pstmt.executeQuery();
        this.dbc.closeConn();
        return OBSReportSubscribers;
    }

    /**
     * Henter alle aktuelle abonnenter for en kunde vha. SQL.
     * @param customerID
     * @return Et ResultSet-objekt med alle aktuelle abonnenter for en kunde.
     * @throws Exception
     */
    public ResultSet getCurrentCustomerOBSReportSubscribers(int customerID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_obsreportsubscriber WHERE obsreportsubscriberid IN (SELECT obsreportsubscriberid FROM view_current WHERE customerID=?)");
        pstmt.setInt(1, customerID);
        ResultSet customers =  pstmt.executeQuery();
        this.dbc.closeConn();
        return customers;
    }

    //</editor-fold>

    //<editor-fold desc="Abonnement">

    /**
     * Opretter et abonnement vha. SQL.
     *
     * @param batchReportSystemID
     * @param OBSReportSubscriberID
     * @throws Exception
     */
    public void createOBSReportSubscription(int batchReportSystemID, int OBSReportSubscriberID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("INSERT INTO tbl_obsreportsubscription(FK_batchReportSystemID, FK_OBSReportSubscriberID) VALUES(?, ?)");
        pstmt.setInt(1, batchReportSystemID);
        pstmt.setInt(2, OBSReportSubscriberID);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    /**
     * Sletter et abonnement vha. SQL.
     *
     * @param OBSReportSubscriptionID
     * @throws Exception
     */
    public void deleteOBSReportSubscription(int OBSReportSubscriberID) throws SQLException {
        // Slet sendlogs tilhørende abonnementet
        PreparedStatement pstmt = this.dbc.createPreparedStatement("DELETE FROM tbl_obsreportsendlog WHERE FK_OBSReportSubscriptionID IN (SELECT OBSReportSubscriptionID FROM tbl_obsreportsubscription WHERE FK_OBSReportSubscriberID=?)");
        pstmt.setInt(1, OBSReportSubscriberID);
        pstmt.executeUpdate();

        // Slet abonnementet
        pstmt = this.dbc.createPreparedStatement("DELETE FROM tbl_obsreportsubscription WHERE FK_OBSReportSubscriberID=?");
        pstmt.setInt(1, OBSReportSubscriberID);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    /**
     * Henter et abonnement vha. SQL.
     *
     * @param OBSReportSubscriptionID
     * @return Et ResultSet-objekt med ét abonnement.
     * @throws Exception
     */
    public ResultSet getOBSReportSubscription(int OBSReportSubscriptionID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_obsreportsubscription WHERE OBSReportSubscriptionID=?");
        pstmt.setInt(1, OBSReportSubscriptionID);
        ResultSet OBSReportSubscriptionDetails = pstmt.executeQuery();
        this.dbc.closeConn();
        return OBSReportSubscriptionDetails;
    }

    /**
     * Henter alle abonnementer for en abonnent vha. SQL.
     *
     * @param OBSReportSubscriberID
     * @return Et ResultSet-objekt med alle abonnementer for en abonnent.
     * @throws Exception
     */
    public ResultSet getOBSReportSubscriberOBSReportSubscriptions(int OBSReportSubscriberID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_batchreportsystem WHERE batchReportSystemID IN (SELECT FK_batchReportSystemID FROM tbl_obsreportsubscription WHERE FK_OBSReportSubscriberID=?)");
        pstmt.setInt(1, OBSReportSubscriberID);
        ResultSet OBSReportSubscritions = pstmt.executeQuery();
        this.dbc.closeConn();
        return OBSReportSubscritions;
    }

     /**
     * Henter alle aktuelle abonnementer for en abonnent.
     *
     * @param OBSReportSubscriberID
     * @return Et ResultSet-objekt med alle aktuelle abonnementer for en abonnent,
     * @throws Exception
     */
    public ResultSet getCurrentOBSReportSubscriberOBSReportSubscriptions(int OBSReportSubscriberID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_batchreportsystem WHERE batchReportSystemID IN (SELECT batchReportSystemID FROM view_current WHERE OBSReportSubscriberID=?)");
        pstmt.setInt(1, OBSReportSubscriberID);
        ResultSet OBSReportSubscritions = pstmt.executeQuery();
        this.dbc.closeConn();
        return OBSReportSubscritions;
    }

    //</editor-fold>

    //<editor-fold desc="Batchrapporter">

    /**
     * Opretter en batchrapport vha. SQL.
     *
     * @param batchReportSystemID
     * @param batchReportName
     * @param batchStartTime
     * @param batchEndTime
     * @param success
     * @param statusMessage
     * @throws Exception
     */
    public void createBatchReport(int batchReportSystemID, String batchReportName, String batchStartTime, String batchEndTime, Boolean success, String statusMessage) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("INSERT INTO tbl_batchreport(BatchReportName, FK_batchReportSystemID, BatchStartTime, BatchEndTime, Success, StatusMessage) VALUES(?, ?, ?, ?, ?, ?)");
        pstmt.setString(1, batchReportName);
        pstmt.setInt(2, batchReportSystemID);
        pstmt.setString(3, batchStartTime);
        pstmt.setString(4, batchEndTime);
        pstmt.setBoolean(5, success);
        pstmt.setString(6, statusMessage);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    /**
     * Henter en batchrapport vha. SQL.
     *
     * @param batchReportID
     * @return Et ResultSet-objekt med én batchrapport.
     * @throws Exception
     */
    public ResultSet getBatchReport(int batchReportID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_batchreport WHERE batchReportID=?");
        pstmt.setInt(1, batchReportID);
        ResultSet batchReport = pstmt.executeQuery();
        this.dbc.closeConn();
        return batchReport;
    }

    /**
     * Opretter et detaljesæt vha. SQL.
     *
     * @param batchReportID
     * @param detailSetName
     * @param totalUpdates
     * @param totalCreates
     * @param totalDeletes
     * @param totalErrors
     * @param totalSkips
     * @throws Exception
     */
    public void createBatchReportDetailSet(int batchReportID, String detailSetName, int totalUpdates, int totalCreates, int totalDeletes, int totalErrors, int totalSkips) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("INSERT INTO tbl_batchreportdetailset (FK_batchReportID, detailSetName, totalUpdates, totalCreates, totalDeletes, totalErrors, totalSkips) VALUES(?, ?, ?, ?, ?, ?, ?)");
        pstmt.setInt(1, batchReportID);
        pstmt.setString(2, detailSetName);
        pstmt.setInt(3, totalUpdates);
        pstmt.setInt(4, totalCreates);
        pstmt.setInt(5, totalDeletes);
        pstmt.setInt(6, totalErrors);
        pstmt.setInt(7, totalSkips);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    /**
     * Henter et detaljesæt vha. SQL.
     *
     * @param batchReportDetailSetID
     * @return Et ResultSet-objekt med ét detaljesæt.
     * @throws Exception
     */
    public ResultSet getBatchReportDetailSet(int batchReportDetailSetID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_batchreportdetailset WHERE batchReportDetailSetID=?");
        pstmt.setInt(1, batchReportDetailSetID);
        ResultSet batchReportSystemDetailSet = pstmt.executeQuery();
        this.dbc.closeConn();
        return batchReportSystemDetailSet;
    }

    /**
     * Henter alle detaljesæt for en batchrapport vha. SQL.
     *
     * @param batchReportID
     * @return Et ResultSet-objekt med alle detaljesæt for en batchrapport.
     * @throws Exception
     */
    public ResultSet getBatchReportDetailSets(int batchReportID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_batchreportdetailset WHERE FK_batchReportID=?");
        pstmt.setInt(1, batchReportID);
        ResultSet batchReportSystemDetailSets = pstmt.executeQuery();
        this.dbc.closeConn();
        return batchReportSystemDetailSets;
    }

    /**
     * Sletter en batchrapport vha. SQL.
     *
     * @param batchReportID
     * @throws Exception
     */
    public void deleteBatchReport(int batchReportID) throws SQLException {
        // Slet sendlogs tilhørende batchrapporten
        PreparedStatement pstmt = this.dbc.createPreparedStatement("DELETE FROM tbl_obsreportsendlog WHERE FK_batchReportID=?");
        pstmt.setInt(1, batchReportID);
        pstmt.executeUpdate();

        // Slet batchrapportdetaljesæt tilhørende batchrapporten
        pstmt = this.dbc.createPreparedStatement("DELETE FROM tbl_batchreportdetailset WHERE FK_BatchReportID=?");
        pstmt.setInt(1, batchReportID);
        pstmt.executeUpdate();

        // Slet batchrapporten
        pstmt = this.dbc.createPreparedStatement("DELETE FROM tbl_batchreport WHERE batchReportID=?");
        pstmt.setInt(1, batchReportID);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    /**
     * Henter alle batchrapporter for et batchrapportsystem vha. SQL.
     * @param batchReportSystemID
     * @return Et ResultSet-objekt med alle batchrapporter for et batchrapportsystem.
     * @throws Exception
     */
    public ResultSet getBatchReportSystemBatchReports(int batchReportSystemID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_batchreport WHERE FK_batchReportSystemID=?");
        pstmt.setInt(1, batchReportSystemID);
        ResultSet customers =  pstmt.executeQuery();
        this.dbc.closeConn();
        return customers;
    }

    /**
     * Henter alle aktuelle batchrapporter for et batchrapportsystem vha. SQL.
     *
     * @param batchReportSystemID
     * @return Et ResultSet-objekt med alle aktuelle batchrapporter for et batchrapportsystem.
     * @throws Exception
     */
    public ResultSet getCurrentBatchReportSystemBatchReports(int batchReportSystemID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_batchreport WHERE batchReportID IN (SELECT batchReportID FROM view_current WHERE batchReportSystemID=?)");
        pstmt.setInt(1, batchReportSystemID);
        ResultSet customers =  pstmt.executeQuery();
        this.dbc.closeConn();
        return customers;
    }

    //</editor-fold>

    //<editor-fold desc="Andet">

    /**
     * Henter alle aktuelle uafsendte batchrapporter vha. SQL.
     *
     * @return Et ResultSet-objekt med alle aktuelle uafsendte batchrapporter.
     * @throws Exception
     */
    public ResultSet getBatchReportCurrentUnsent() throws SQLException {
        Statement stmt = this.dbc.createStatement();
        ResultSet BatchReportCurrentUnsent = stmt.executeQuery("SELECT batchReportID, OBSReportSubscriptionID, OBSReportSubscriberID FROM view_current WHERE timeSent IS null AND batchReportID IS NOT null");
        this.dbc.closeConn();
        return BatchReportCurrentUnsent;
    }

    /**
     * Henter et batchrapportinterval vha. SQL.
     *
     * @param batchReportIntervalID
     * @return Et ResultSet-objekt med ét batchrapportinterval
     * @throws Exception
     */
    public ResultSet getBatchReportInterval(int batchReportIntervalID) throws SQLException {
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT * FROM tbl_batchreportinterval WHERE batchreportIntervalID=?");
        pstmt.setInt(1, batchReportIntervalID);
        ResultSet rs = pstmt.executeQuery();
        this.dbc.closeConn();
        return rs;
    }

    /**
     * Henter alle batchrapportintervaller vha. SQL.
     *
     * @return Et ResultSet-objekt med alle batchrapportintervaller.
     * @throws Exception
     */
    public ResultSet getBatchReportIntervals() throws SQLException {
        Statement stmt = this.dbc.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM tbl_batchreportinterval");
        this.dbc.closeConn();
        return rs;
    }

    /**
     * Registrerer status vedrørende afsendelse af batchrapporter vha SQL.
     *
     * @param OBSReportSubscriberID
     * @param statusMessage
     * @param BatchReportID
     * @throws Exception
     */
    public void registerInSendLog (int OBSReportSubscriberID, String statusMessage, int BatchReportID) throws SQLException {
        // Find subscriptionID
        PreparedStatement pstmt = this.dbc.createPreparedStatement("SELECT DISTINCT OBSReportSubscriptionID FROM tbl_obsreportsubscription WHERE fk_batchreportsystemid IN (SELECT fk_batchreportsystemid FROM tbl_batchreport WHERE batchreportid=?) AND FK_OBSReportSubscriberID=?");
        pstmt.setInt(1, BatchReportID);
        pstmt.setInt(2, OBSReportSubscriberID);
        ResultSet rs = pstmt.executeQuery();
        rs.next();
        int OBSReportSubscriptionID = rs.getInt(1);

        pstmt = this.dbc.createPreparedStatement("INSERT INTO tbl_obsreportsendlog(FK_OBSReportSubscriptionID, TimeSent, StatusMessage, FK_BatchReportID) VALUES (?,Now(),?,?)");
        pstmt.setInt(1, OBSReportSubscriptionID);
        pstmt.setString(2, statusMessage);
        pstmt.setInt(3, BatchReportID);
        pstmt.executeUpdate();
        this.dbc.closeConn();
    }

    //</editor-fold>
}
